//
//  SongFMDBHelper.swift
//  LGMusic
//
//  Created by 梁尚嘉 on 2017/3/31.
//  Copyright © 2017年 LimeGah. All rights reserved.
//

import UIKit
import FMDB

class SongFMDBHelper: FMDBHelper {


    class func songTableName() -> String {
        return "SONG_TABLE"
    }

    class func checkBillTable (_ dataBase:FMDatabase) -> Bool
    {
        let SQL = "CREATE TABLE IF NOT EXISTS "+self.songTableName()+" (queryId text NOT NULL,songId text NOT NULL,songName text NOT NULL,artistId text NOT NULL,artistName text NOT NULL,albumId text NOT NULL,albumName text NOT NULL,songPicSmall text NOT NULL,songPicBig text NOT NULL,songPicRadio text NOT NULL,lrcLink text NOT NULL,time text NOT NULL,songLink text NOT NULL,showLink text NOT NULL,format text NOT NULL,rate integer,size integer,source text NOT NULL);"
        do {
            try dataBase.executeUpdate(SQL, values: nil)
            return true
        }catch {
            print(error)
            return false
        }
    }

    class func updateSongTable(_ song:Song,
                               isFavorite:Bool,
                        block:@escaping ((_ success:Bool)->Swift.Void)
        ) {

        self.FMDBQueue().inTransaction({ (db, rollback) in

            do {
                if self.checkBillTable(db!) {
                    if isFavorite {
                        try db?.executeUpdate("INSERT INTO "+self.songTableName()+" VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
                                              values: [
                                                song.queryId as Any,
                                                song.songId as Any,
                                                song.songName as Any,
                                                song.artistId as Any,
                                                song.artistName as Any,
                                                song.albumId as Any,
                                                song.albumName as Any,
                                                song.songPicSmall as Any,
                                                song.songPicBig as Any,
                                                song.songPicRadio as Any,
                                                song.lrcLink as Any,
                                                song.time as Any,
                                                song.songLink as Any,
                                                song.showLink as Any,
                                                song.format as Any,
                                                song.rate as Any,
                                                song.size as Any,
                                                song.source as Any
                            ])
                    }else {
                        try db?.executeUpdate("DELETE FROM "+self.songTableName()+" WHERE songId="+song.songId!, values: nil)
                    }

                    block(true)
                }else {
                    block(false)
                }
            } catch {
                block(false)
                rollback?.pointee = true
                print(error)
            }

        })

    }


    class func getFavoriteSongs(_ block:@escaping ((_ bills:[Song])->Void)) {

        self.FMDBQueue().inTransaction({ (db, rollback) in
            do {
                if self.checkBillTable(db!) {
                    let result = try db?.executeQuery("SELECT * FROM "+self.songTableName()+" ORDER BY artistName DESC", values: nil)
                    var resultArray = [Song]()
                    while (result?.next())! {
                        let jsonData = try JSONSerialization.data(withJSONObject: result?.resultDictionary()!, options: JSONSerialization.WritingOptions.prettyPrinted)
                        let jsonString = String.init(data: jsonData, encoding: String.Encoding.utf8)
                        resultArray.append(Song.deserialize(from: jsonString)!)
                    }
                    block(resultArray)
                }else {
                    block([])
                }
            } catch {
                block([])
                rollback?.pointee = true
                print(error)
            }
        })
    }
    
    

}
